//-----------------------------------------------------------------------
// <copyright file="SQLProvider.cs" company="Simnova LLC">
//     Copyright (c) Simnova LLC. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------

namespace Simnova.Collaborate.ServiceImplementation.SQLProvider
{
    using System;
    using System.Collections.Generic;
    using System.Text;
    using Simnova.Collaborate.ServiceContract.DataClasses;
    using System.Globalization;

    /// <summary>
    /// The SQLProvider Implementation of the DataProvider class.
    /// </summary>
    public class SQLProvider : Simnova.Collaborate.ServiceImplementation.DataProvider
    {
        #region NHibernate

        private NHibernate.ISessionFactory factory;

        public SQLProvider()
        {
            NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();
            cfg.SetProperty("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");
            cfg.SetProperty("hibernate.dialect", "NHibernate.Dialect.MsSql2005Dialect");
            cfg.SetProperty("hibernate.connection.driver_class", "NHibernate.Driver.SqlClientDriver");
           
            cfg.SetProperty("hibernate.connection.connection_string", @"Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|Collaborate.mdf;Integrated Security=True;User Instance=True");


            cfg.SetProperty("hibernate.show_sql", "true");
            cfg.AddAssembly("Simnova.Collaborate.ServiceImplementation.SQLProvider");
            this.factory = cfg.BuildSessionFactory();
        }

        ~SQLProvider()
        {
            this.factory.Close();
        }
        #endregion

        #region Company

        public override Company GetCompany(int companyId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Company p WHERE p.companyId = :CompanyId");
            query.SetString("CompanyId", companyId.ToString(CultureInfo.InvariantCulture));
            Company result = query.UniqueResult<Company>();
            session.Close();
            return result;
        }

        public override void SaveCompany(Company company, int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.ITransaction trans = session.BeginTransaction();
            try
            {
                session.SaveOrUpdate(company);
                CompanyUser companyUser = new CompanyUser();
                companyUser.UserId = userId;
                companyUser.CompanyId = company.CompanyId;
                companyUser.IsAdmin = true;
                session.SaveOrUpdate(companyUser);
            }
            catch 
            {
                trans.Rollback();
                throw;
            }

            trans.Commit();
            session.Flush();
            session.Close();
        }

        public override IList<Company> GetCompanies()
        {
            NHibernate.ISession session = this.factory.OpenSession();           
            IList<Company> result = session.CreateCriteria(typeof(Company)).List<Company>();
            session.Close();
            return result;
        }

        #endregion

        #region Project

        public override Project GetProject(int projectId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Project p WHERE p.projectId = :ProjectId");
            query.SetString("ProjectId", projectId.ToString(CultureInfo.InvariantCulture));
            Project result = query.UniqueResult<Project>();
            session.Close();
            return result;
        }

        public override void SaveProject(Project project)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(project);
            session.Flush();
        }

        public override IList<Project> GetProjectsByCompany(int companyId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = this.factory.OpenSession().CreateQuery("FROM Project p WHERE p.companyId = :CompanyId");
            query.SetString("CompanyId", companyId.ToString(CultureInfo.InvariantCulture));
            IList<Project> result = query.List<Project>();
            session.Close();
            return result;
        }

        #endregion

        #region Task

        public override Task GetTask(int taskId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Task p WHERE p.taskId = :TaskId");
            query.SetString("TaskId", taskId.ToString(CultureInfo.InvariantCulture));
            Task result = query.UniqueResult<Task>();
            session.Close();
            return result;
        }

        public override void SaveTask(Task task)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(task);
            session.Flush();
        }

        public override void AssignTask(int taskId, int userId)
        {
            UserTask ut = new UserTask();
            ut.TaskId = taskId;
            ut.UserId = userId;
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(ut);
            session.Flush();
        }

        public override IList<Task> GetTasksByProject(int projectId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = this.factory.OpenSession().CreateQuery("FROM Task t WHERE t.projectId = :ProjectId");
            query.SetString("ProjectId", projectId.ToString(CultureInfo.InvariantCulture));
            IList<Task> result = query.List<Task>();
            session.Close();
            return result;
        }

        public override IList<User> GetUsersByTask(int taskId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("SELECT u FROM User u, UserTask ut WHERE ut.taskId = :TaskId");
            query.SetString("TaskId", taskId.ToString(CultureInfo.InvariantCulture));
            IList<User> result = query.List<User>();
            session.Close();
            return result;
        }

        public override IList<Task> GetTasksByUser(int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("SELECT t FROM Task t, UserTask ut WHERE ut.userId = :UserId");
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            IList<Task> result = query.List<Task>();
            session.Close();
            return result;
        }
        #endregion

        #region User

        public override void SaveUser(User user)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(user);
            session.Flush();
        }

        public override User GetUserById(int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM User u WHERE u.userId = :UserId");
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            User result = query.UniqueResult<User>();
            session.Close();
            return result;
        }

        public override User GetUserByEmailAddress(string emailAddress)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM User u WHERE u.emailAddress = :emailAddress");
            query.SetString("emailAddress", emailAddress);
            User result = query.UniqueResult<User>();
            session.Close();
            return result;
        }

        public override User GetUser(string emailAddress, string password)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM User u WHERE u.emailAddress = :emailAddress AND u.password = :password");
            query.SetString("emailAddress", emailAddress.ToString());
            query.SetString("password", password.ToString());
            User result = query.UniqueResult<User>();
            session.Close();
            return result;
        }

        public override IList<CompanyUser> GetCompanyUsersByCompany(int companyId)
        {
            NHibernate.IQuery query = this.factory.OpenSession().CreateQuery("FROM CompanyUser cu WHERE cu.companyId = :CompanyId");
            query.SetString("CompanyId", companyId.ToString(CultureInfo.InvariantCulture));
            return query.List<CompanyUser>();
        }

        public override IList<User> GetUsers()
        {
            return this.factory.OpenSession().CreateCriteria(typeof(User)).List<User>();
        }

        public override IList<Company> GetCompaniesByAdministrator(int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("SELECT c FROM Company c, CompanyUser cu WHERE c.companyId = cu.companyId AND cu.isAdmin = 1 AND cu.userId = :UserId");
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            IList<Company> result = query.List<Company>();
            session.Close();
            return result;
        }

        public override IList<Company> GetCompaniesByUser(int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("SELECT c FROM Company c, CompanyUser cu WHERE c.companyId = cu.companyId AND cu.userId = :UserId");
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            IList<Company> result = query.List<Company>();
            session.Close();
            return result;
        }

        public override void SaveUserRole(UserRole userRole)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(userRole);
            session.Flush();
        }

        public override void SaveActivityType(ActivityType activityType)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(activityType);
            session.Flush();
        }

        public override IList<ActivityType> GetActivityTypesByRole(int roleId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM ActivityType at WHERE at.roleId = :RoleId");
            query.SetString("RoleId", roleId.ToString(CultureInfo.InvariantCulture));
            IList<ActivityType> result = query.List<ActivityType>();
            session.Close();
            return result;
        }

        public override IList<Role> GetRolesByCompany(int companyId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Role r WHERE r.companyId = :CompanyId");
            query.SetString("CompanyId", companyId.ToString(CultureInfo.InvariantCulture));
            IList<Role> result = query.List<Role>();
            session.Close();
            return result;
        }

        public override IList<Role> GetRolesByUserCompany(int userId, int companyId)
        {
            NHibernate.IQuery query = this.factory.OpenSession().CreateQuery("SELECT r FROM Role r, UserRole ur WHERE r.roleId = ur.roleId AND ur.userId = :UserId AND r.companyId = :CompanyId");
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            query.SetString("CompanyId", companyId.ToString(CultureInfo.InvariantCulture));
            return query.List<Role>();
        }

        public override void SaveRole(Role role)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(role);
            session.Flush();
        }

        #endregion

        #region TimeLog

        public override IList<TimeLog> GetTimeLogByUser(int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM TimeLog t WHERE t.userId = :UserId ORDER BY t.createDate DESC").SetMaxResults(20);
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            IList<TimeLog>  result = query.List<TimeLog>();
            session.Close();
            return result;
        }

        public override TimeLog GetCurrentTimeLog(int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM TimeLog t WHERE t.endTime IS NULL AND t.userId = :userId");
            query.SetString("userId", userId.ToString());
            TimeLog timeLog = query.UniqueResult<TimeLog>();
            if (timeLog == null)
            {
                timeLog = new TimeLog();
                timeLog.UserId = userId;
                this.UpdateTimeLog(timeLog);
            }
            session.Close();
            return timeLog;
        }

        public void UpdateTimeLog(TimeLog timeLog)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(timeLog);
            session.Flush();
        }

        public override void SaveTimeLog(TimeLog timeLog)
        {
            timeLog.EndTime = System.DateTime.Now;
            timeLog.Duration = System.Convert.ToInt32(timeLog.EndTime.Subtract(timeLog.StartTime).TotalMinutes);
            this.UpdateTimeLog(timeLog);
        }

        public override void DeleteTimeLog(TimeLog timeLog)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.Delete(timeLog);
            session.Flush();
        }

        #endregion

        #region Feedback

        public override IList<Product> GetProductsByCompany(int companyId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Product p WHERE p.companyId = :CompanyId");
            query.SetString("CompanyId", companyId.ToString(CultureInfo.InvariantCulture));
            IList<Product> result = query.List<Product>();
            session.Close();
            return result;
        }

        public override Product GetProduct(int productId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Product p WHERE p.productId = :ProductId");
            query.SetString("ProductId", productId.ToString(CultureInfo.InvariantCulture));
            Product result = query.UniqueResult<Product>();
            session.Close();
            return result;
        }

        public override Feedback GetFeedback(int feedbackId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Feedback f WHERE f.feedbackId = :FeedbackId");
            query.SetString("FeedbackId", feedbackId.ToString(CultureInfo.InvariantCulture));
            Feedback result = query.UniqueResult<Feedback>();
            session.Close();
            return result;
        }

        public override FeedbackComment GetFeedbackCommentByFeedback(int userId, int feedbackId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM FeedbackComment f WHERE f.feedbackId = :FeedbackId AND f.userId = :UserId");
            query.SetString("FeedbackId", feedbackId.ToString(CultureInfo.InvariantCulture));
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            FeedbackComment result = query.UniqueResult<FeedbackComment>();
            session.Close();
            return result;
        }

        public override FeedbackRating GetFeedbackRatingByFeedback(int userId, int feedbackId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM FeedbackRating f WHERE f.feedbackId = :FeedbackId AND f.userId = :UserId");
            query.SetString("FeedbackId", feedbackId.ToString(CultureInfo.InvariantCulture));
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            FeedbackRating result = query.UniqueResult<FeedbackRating>();
            session.Close();
            return result;
        }

        public override FeedbackWatchList GetFeedbackWatchListByFeedback(int userId, int feedbackId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM FeedbackWatchList f WHERE f.feedbackId = :FeedbackId AND f.userId = :UserId");
            query.SetString("FeedbackId", feedbackId.ToString(CultureInfo.InvariantCulture));
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            FeedbackWatchList result = query.UniqueResult<FeedbackWatchList>();
            session.Close();
            return result;
        }

        public override FeedbackWorkaround GetFeedbackWorkaroundByFeedback(int userId, int feedbackId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM FeedbackWorkaround f WHERE f.feedbackId = :FeedbackId AND f.userId = :UserId");
            query.SetString("FeedbackId", feedbackId.ToString(CultureInfo.InvariantCulture));
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            FeedbackWorkaround result = query.UniqueResult<FeedbackWorkaround>();
            session.Close();
            return result;
        }

        public override IList<Feedback> GetProductFeedback(int productId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Feedback f WHERE f.productId = :ProductId");
            query.SetString("ProductId", productId.ToString(CultureInfo.InvariantCulture));
            IList<Feedback> result = query.List<Feedback>();
            session.Close();
            return result;
        }

        public override IList<Feedback> GetTopRatedProductFeedback(int productId, int quantity)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Feedback f WHERE f.productId = :ProductId ORDER BY f.averageRating");
            query.SetString("ProductId", productId.ToString(CultureInfo.InvariantCulture));
            IList<Feedback> result = query.SetMaxResults(quantity).List<Feedback>();
            session.Close();
            return result;
        }

        public override IList<Feedback> GetWatchListProductFeedback(int productId, int userId, int quantity)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("SELECT f FROM Feedback f, FeedbackWatchList fw WHERE f.feedbackId = fw.feedbackId AND f.productId = :ProductId AND fw.userId = :UserId ORDER BY f.createDate");
            query.SetString("ProductId", productId.ToString(CultureInfo.InvariantCulture));
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            IList<Feedback> result = query.SetMaxResults(quantity).List<Feedback>();
            session.Close();
            return result;
        }

        public override IList<Feedback> GetNewestProductFeedback(int productId, int quantity)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Feedback f WHERE f.productId = :ProductId ORDER BY f.createDate");
            query.SetString("ProductId", productId.ToString(CultureInfo.InvariantCulture));
            IList<Feedback> result = query.SetMaxResults(quantity).List<Feedback>();
            session.Close();
            return result;
        }

        public override IList<Feedback> GetProductFeedbackByUser(int productId, int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM Feedback f WHERE f.productId = :ProductId AND f.userId = :UserId");
            query.SetString("ProductId", productId.ToString(CultureInfo.InvariantCulture));
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            IList<Feedback> result = query.List<Feedback>();
            session.Close();
            return result;
        }

        public override IList<FeedbackWatchList> GetFeedbackWatchListByUser(int productId, int userId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM FeedbackWatchList f WHERE f.productId = :ProductId AND f.userId = :UserId");
            query.SetString("ProductId", productId.ToString(CultureInfo.InvariantCulture));
            query.SetString("UserId", userId.ToString(CultureInfo.InvariantCulture));
            IList<FeedbackWatchList> result = query.List<FeedbackWatchList>();
            session.Close();
            return result;
        }

        public override IList<FeedbackComment> GetFeedbackComments(int feedbackId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM FeedbackComment f WHERE f.feedbackId = :FeedbackId");
            query.SetString("FeedbackId", feedbackId.ToString(CultureInfo.InvariantCulture));
            IList<FeedbackComment> result = query.List<FeedbackComment>();
            session.Close();
            return result;
        }

        public override IList<FeedbackRating> GetFeedbackRatings(int feedbackId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM FeedbackRating f WHERE f.feedbackId = :FeedbackId");
            query.SetString("FeedbackId", feedbackId.ToString(CultureInfo.InvariantCulture));
            IList<FeedbackRating> result = query.List<FeedbackRating>();
            session.Close();
            return result;
        }

        public override IList<FeedbackWorkaround> GetFeedbackWorkarounds(int feedbackId)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            NHibernate.IQuery query = session.CreateQuery("FROM FeedbackWorkaround f WHERE f.feedbackId = :FeedbackId");
            query.SetString("FeedbackId", feedbackId.ToString(CultureInfo.InvariantCulture));
            IList<FeedbackWorkaround> result = query.List<FeedbackWorkaround>();
            session.Close();
            return result;
        }

        public override void SaveProduct(Product product)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(product);
            session.Flush();
        }

        public override void SaveFeedback(Feedback feedback)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(feedback);
            session.Flush();
        }

        public override void SaveFeedbackComment(FeedbackComment feedbackComment)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(feedbackComment);
            session.Flush();
        }

        public override void SaveFeedbackRating(FeedbackRating feedbackRating)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(feedbackRating);
            session.Flush();
        }

        public override void SaveFeedbackWatchList(FeedbackWatchList feedbackWatchList)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(feedbackWatchList);
            session.Flush();
        }

        public override void DeleteFeedbackWatchList(FeedbackWatchList feedbackWatchList)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.Delete(feedbackWatchList);
            session.Flush();
        }

        public override void SaveFeedbackWorkaround(FeedbackWorkaround feedbackWorkaround)
        {
            NHibernate.ISession session = this.factory.OpenSession();
            session.SaveOrUpdate(feedbackWorkaround);
            session.Flush();
        }

        #endregion
    }
}
